PostgreSQL で曖昧なカラムのみで ORDER BY しつつ LIMIT をかけると行が重複することがある

なにこれ?

code:model.ts

type User = {

id: number

name: string

password: string

role: 'admin' | 'user'

createdAt: Date

updatedAt: Date

}

のようなテーブルに対して

code:bad_query.sql

SELECT * FROM users ORDER BY role asc LIMIT 3 OFFSET 0;

SELECT * FROM users ORDER BY role asc LIMIT 3 OFFSET 3;

みたいなクエリを発行すると

table:bad-result-0

id name password role createdAt updatedAt

1 hoge **** admin 2021-12-09 14:03:00 2021-12-09 14:03:00

3 puni **** admin 2021-12-09 14:03:02 2021-12-09 14:03:02

2 piyo **** user 2021-12-09 14:03:01 2021-12-09 14:03:01

table:bad-result-1

id name password role createdAt updatedAt

2 piyo **** user 2021-12-09 14:03:01 2021-12-09 14:03:01

4 poge **** user 2021-12-09 14:03:03 2021-12-09 14:03:03

5 poyo **** user 2021-12-09 14:03:04 2021-12-09 14:03:04

のように別ページで重複した結果(今回であれば id: 2)が返ってくることがある

LIMIT をかけなければ重複しない

これは role というソート順が曖昧なカラム(unique じゃないという事です)のみでソートをしているから

role でソートしたあとの順序はエンジン側の勝手なので LIMIT を考慮してくれないこともある

対処方法は unique なカラムでソートをかける

code:good_query.sql

SELECT * FROM users ORDER BY role asc, id asc LIMIT 3 OFFSET 0;

SELECT * FROM users ORDER BY role asc, id asc LIMIT 3 OFFSET 3;

とすれば

table:good-result-0

id name password role createdAt updatedAt

1 hoge **** admin 2021-12-09 14:03:00 2021-12-09 14:03:00

3 puni **** admin 2021-12-09 14:03:02 2021-12-09 14:03:02

2 piyo **** user 2021-12-09 14:03:01 2021-12-09 14:03:01

table:good-result-1

id name password role createdAt updatedAt

4 poge **** user 2021-12-09 14:03:03 2021-12-09 14:03:03

5 poyo **** user 2021-12-09 14:03:04 2021-12-09 14:03:04

6 poge **** user 2021-12-09 14:03:05 2021-12-09 14:03:05

のようになる